部分列重复的数据，通常表上是有主键的，可能是程序逻辑造成了多行数据列值的重复。
select distinct * into #tmp
create table duplicate_all
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
)
insert into duplicate_all
from duplicate_all
select * from vitae a
drop table duplicate_all
c1 int,

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'

一. 删除完全重复的记录

drop table duplicate_col

复制代码 代码如下:

GO
GO
(
as
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'

完全重复的数据，通常是由于没有设置主键/唯一键约束导致的。
where exists(select 1 from duplicate_col b where duplicate_col.c1 > b.c1 and (duplicate_col.c2 = b.c2 and duplicate_col.c3 = b.c3))
where c1 = 1
update vitae set ispass=-1
GO
select 1,100,'aaa' union all

drop table #tmp
用SQL语句,删除掉重复项只保留一条

利用DISTINCT得到单条记录，删除源数据，然后导回不重复记录。
3、查找表中多余的重复记录（多个字段）

select 4,400,'ddd' union all
drop table tmp
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录


select 3,300,'ccc' union all
select * from vitae a
c3 varchar(100),
select * from people
insert into duplicate_all
where c1 not in (select min(c1) from duplicate_col group by c2,c3)

(1) 唯一索引

)

如果多个表有完全重复的行，可以考虑通过UNION将多个表联合，插到一个新的同结构的表，SQL Server会帮助去掉表和表之间的重复行。

select 2,200,'bbb' union all
测试数据：
select 1,100,'aaa' union all
select * from #tmp

where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
(2) 使用ROW_NUMBER
GO
通常会选择主键/唯一键的最大/最小值保留，其他行删除。以下只保留重复记录中c1最小的行。

复制代码 代码如下:

and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
GO
7.消除一个字段的右边的第一位：
和删除完全重复记录的写法基本一样。
(3) ROW_NUMBER

复制代码 代码如下:

6.消除一个字段的左边的第一位：
GO
c1 int,
select 2,100,'aaa' union all

(1) 借助临时表

(
select *,ROW_NUMBER() OVER(PARTITION BY c2,c3 ORDER BY(getdate())) as num
create table duplicate_col
delete from vitae a
if OBJECT_ID('duplicate_all') is not null
c1 int primary key,

二. 删除部分重复的记录

(2) 借助主键/唯一键来删除
(
GO

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
select 1,100,'aaa' union all
如果要保留重复记录中的第N行，可以参考05.取分组中的某几行。
delete tmp where num > 1

复制代码 代码如下:

select * from duplicate_col
)
select * from tmp
select * from duplicate_col
select 5,500,'eee'

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

select 3,100,'aaa' union all
delete tmp where num > 1

复制代码 代码如下:

with tmp
select *,ROW_NUMBER() OVER(PARTITION BY c1,c2,c3 ORDER BY(getdate())) as num
create table tmp

复制代码 代码如下:

insert into duplicate_col
(
select 1,100,'aaa' union all
with tmp
where peopleId in (select peopleId from vitae group by peopleId

复制代码 代码如下:

)
--或者
as
insert into tmp
where c1 = 1

c3 varchar(100)
and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)
select 1,100,'aaa' union all
SQL删除重复数据只保留一条 （下面的代码，很多网友反馈错误，大家多测试）

c2 int,

select 1,100,'aaa' union all
)
c2 int,

复制代码 代码如下:

GO
GO

唯一索引有个忽略重复建的选项，在创建主键约束/唯一键约束时都可以使用这个索引选项。
select 4,100,'aaa' union all

delete from people
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
delete from duplicate_col
8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录
where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)
c3 varchar(100)
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
constraint UQ_01 unique(c2,c3) with(IGNORE_DUP_KEY = ON)
delete from duplicate_col

c2 int,
如果表不大的话，可以把所有记录导出一次，然后truncate表后再导回，这样可以避免delete的日志操作。

GO
select 5,500,'eee'
from duplicate_all
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
(
from duplicate_col
if OBJECT_ID('duplicate_col') is not null
GO
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
if OBJECT_ID('tmp') is not null
delete duplicate_all where c1 = 1
测试数据：

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
if OBJECT_ID('tempdb..#tmp') is not null